--CREATE LANGUAGE plpgsql;
CREATE or REPLACE function jbpmCreditos.cre_enumerar_bandejas_posibles
(usuario in varchar(20), seguridad in integer)

returns setof record 

as 
'
select distinct o.ofi_id, o.ofi_nombre from 
jbpmCreditos.jbpm4_id_user u 
inner join jbpmCreditos.jbpm4_id_membership m on u.dbid_=m.user_
inner join jbpmCreditos.jbpm4_id_group g on m.group_=g.dbid_
inner join jbpmCreditos.sec_grupo_oficina go on g.id_=go.gru_grupo
inner join jbpmCreditos.sec_permisos_oficina p on go.ofi_id=p.per_observador
inner join jbpmCreditos.sec_oficina o on p.per_destino=o.ofi_id
where p.seg_id <= $2 and u.id_ = $1
order by o.ofi_nombre;
'

LANGUAGE sql;


/*
select * from jbpmCreditos.cre_enumerar_bandejas_posibles('fbarletta', 3) 
		as foo(ofi_id integer,ofi_nombre varchar(50));


 ofi_id | ofi_nombre
--------+------------
     10 | UEP Sta Fe
(1 fila)

*/






/*
cre_mostrar_bandeja
*/

CREATE or REPLACE function jbpmCreditos.cre_mostrar_bandeja (usuario varchar(20), seguridad int, bandeja int)
returns setof record 

as 
'
select e.id_, t.assignee_, e.activityname_, t.dbid_, t.assignee_ as assignee_2
  from jbpmCreditos.jbpm4_task t
        inner join jbpmCreditos.jbpm4_execution e on t.execution_=e.dbid_
        inner join jbpmCreditos.jbpm4_swimlane s on t.swimlane_=s.dbid_
        left join jbpmCreditos.jbpm4_participation p on s.dbid_=p.swimlane_
		inner join jbpmCreditos.jbpm4_id_user u on t.assignee_ = u.id_
		inner join jbpmCreditos.jbpm4_id_membership m on u.dbid_=m.user_
		inner join jbpmCreditos.jbpm4_id_group g on m.group_=g.dbid_
		inner join jbpmCreditos.sec_grupo_oficina o on g.id_=o.gru_grupo
	where p.dbid_ is null
and o.ofi_id in (
    select distinct p.per_destino from
        jbpmCreditos.jbpm4_id_user u
        inner join jbpmCreditos.jbpm4_id_membership m on u.dbid_=m.user_
        inner join jbpmCreditos.jbpm4_id_group g on m.group_=g.dbid_
        inner join jbpmCreditos.sec_grupo_oficina o on g.id_=o.gru_grupo
        inner join jbpmCreditos.sec_permisos_oficina p on o.ofi_id=p.per_observador
        where u.id_=$1 and p.seg_id <=$2
    )
and o.ofi_id=$3

union

select distinct e.id_, t.assignee_, e.activityname_, t.dbid_, case when u.id_=$1 then $1 else null end as assignee_2
  from jbpmCreditos.jbpm4_task t
        inner join jbpmCreditos.jbpm4_execution e on t.execution_=e.dbid_
        inner join jbpmCreditos.jbpm4_participation p on p.task_=t.dbid_
        inner join jbpmCreditos.sec_grupo_oficina o on p.groupID_ = o.gru_grupo
        inner join jbpmCreditos.sec_oficina o2 on o.ofi_id=o2.ofi_id
left join jbpmCreditos.jbpm4_id_group g on o.gru_grupo=g.id_       
inner join jbpmCreditos.jbpm4_id_membership m on g.dbid_=m.group_
inner join jbpmCreditos.jbpm4_id_user u on m.user_=u.dbid_ and u.id_=$1
where o.ofi_id in (
    select distinct p.per_destino from
        jbpmCreditos.jbpm4_id_user u
        inner join jbpmCreditos.jbpm4_id_membership m on u.dbid_=m.user_
        inner join jbpmCreditos.jbpm4_id_group g on m.group_=g.dbid_
        inner join jbpmCreditos.sec_grupo_oficina o on g.id_=o.gru_grupo
        inner join jbpmCreditos.sec_permisos_oficina p on o.ofi_id=p.per_observador       
        where u.id_=$1 and p.seg_id <=$2
    )
and o.ofi_id=$3


union


select distinct e.id_, t.assignee_, e.activityname_, t.dbid_, case when u.id_=$1 then $1 else null end as assignee_2
  from jbpmCreditos.jbpm4_task t
        inner join jbpmCreditos.jbpm4_execution e on t.execution_=e.dbid_
        inner join jbpmCreditos.jbpm4_participation p on p.task_=t.dbid_
        inner join jbpmCreditos.sec_grupo_oficina o on p.groupID_ = o.gru_grupo
        inner join jbpmCreditos.sec_oficina o2 on o.ofi_id=o2.ofi_id
left join jbpmCreditos.jbpm4_id_group g on o.gru_grupo=g.id_       
inner join jbpmCreditos.jbpm4_id_membership m on g.dbid_=m.group_
inner join jbpmCreditos.jbpm4_id_user u on m.user_=u.dbid_ -- and u.id_=$1
where o.ofi_id in (
    select distinct p.per_destino from
        jbpmCreditos.jbpm4_id_user u
        inner join jbpmCreditos.jbpm4_id_membership m on u.dbid_=m.user_
        inner join jbpmCreditos.jbpm4_id_group g on m.group_=g.dbid_
        inner join jbpmCreditos.sec_grupo_oficina o on g.id_=o.gru_grupo
        inner join jbpmCreditos.sec_permisos_oficina p on o.ofi_id=p.per_observador       
        where u.id_=$1 and p.seg_id <=$2
    )
and o.ofi_id=$3 and not exists
(

            select distinct e.id_, t.assignee_, e.activityname_, t.dbid_, case when u.id_=$1 then $1 else null end as assignee_2
              from jbpmCreditos.jbpm4_task t
                    inner join jbpmCreditos.jbpm4_execution e on t.execution_=e.dbid_
                    inner join jbpmCreditos.jbpm4_participation p on p.task_=t.dbid_
                    inner join jbpmCreditos.sec_grupo_oficina o on p.groupID_ = o.gru_grupo
                    inner join jbpmCreditos.sec_oficina o2 on o.ofi_id=o2.ofi_id
            left join jbpmCreditos.jbpm4_id_group g on o.gru_grupo=g.id_       
            inner join jbpmCreditos.jbpm4_id_membership m on g.dbid_=m.group_
            inner join jbpmCreditos.jbpm4_id_user u on m.user_=u.dbid_ and u.id_=$1
            where o.ofi_id in (
                select distinct p.per_destino from
                    jbpmCreditos.jbpm4_id_user u
                    inner join jbpmCreditos.jbpm4_id_membership m on u.dbid_=m.user_
                    inner join jbpmCreditos.jbpm4_id_group g on m.group_=g.dbid_
                    inner join jbpmCreditos.sec_grupo_oficina o on g.id_=o.gru_grupo
                    inner join jbpmCreditos.sec_permisos_oficina p on o.ofi_id=p.per_observador       
                    where u.id_=$1 and p.seg_id <=$2
                )
            and o.ofi_id=$3

)

order by id_

'
LANGUAGE sql;

/*
usuario, seguridad, bandeja
select * from jbpmCreditos.cre_mostrar_bandeja ('rcarballeira', 3, 34) 
as
foo(id_ varchar(255), assignee_ varchar(255), activityname_ varchar(255), dbid_ bigint, assignee_2 varchar(255))
*/



--



CREATE or REPLACE function jbpmCreditos.gui_mostrar_solapas
(formulario varchar(255), seguridad int, perfil char(1),
proceso varchar(255), version varchar(255), usuario varchar(50))

returns setof record 

as 
'

select s.sol_descripcion, s.sol_archivo, i.inc_tipo, i.inc_url, f.frm_orden, s.sol_on_load  from 
	jbpmCreditos.frm_formularios_solapas f 
	inner join jbpmCreditos.frm_solapas s on f.sol_nombre=s.sol_nombre and s.NAME_=f.NAME_ and s.OBJNAME_=f.OBJNAME_
	left join jbpmCreditos.frm_includes i on s.sol_nombre=i.sol_nombre and s.NAME_=i.NAME_ and s.OBJNAME_=i.OBJNAME_
	inner join jbpmCreditos.sec_permisos_oficina p on f.ofi_id=p.per_observador --per_destino
	inner join jbpmCreditos.sec_grupo_oficina o on p.per_observador=o.ofi_id
	inner join jbpmCreditos.jbpm4_id_group g on o.gru_grupo = g.id_
	inner join jbpmCreditos.jbpm4_id_membership m on g.dbid_ = m.group_
	inner join jbpmCreditos.jbpm4_id_user u on u.dbid_ = m.user_
		

where 
	f.frm_nombre=$1
	and f.seg_id <=$2
	--and f.ofi_id = @oficina
	and f.frm_perfil=$3
	and f.OBJNAME_=$4
	and f.NAME_=$5
	and u.id_ = $6

	--u.id_=$6 
	and p.seg_id <=$2
	group by s.sol_descripcion, s.sol_archivo, i.inc_tipo, i.inc_url, f.frm_orden, s.sol_on_load
	order by f.frm_orden

' LANGUAGE sql;


